package com.matt.repository.impl;

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONObject;
import com.dotflat.util.AssertUtil;
import com.dotflat.util.UuidUtil;
import com.dotflat.util.excelUtil.StringUtils;
import com.matt.commons.dto.sso.StaffDTO;
import com.matt.commons.page.PagerQuery;
import com.matt.domain.Children;
import com.matt.domain.courseV2.ChildrenMonthRecord;
import com.matt.domain.courseV2.vo.ChildrenMonthRecordVO;
import com.matt.domain.rel.RelClassesChildren;
import com.matt.foundation.DO.AdviseDetailDO;
import com.matt.foundation.DO.ChildrenCourseItemDO;
import com.matt.foundation.model.MemVerificationCache;
import com.matt.foundation.utils.DateUtil;
import com.matt.foundation.vo.IDCountVO;
import com.matt.repository.ChildrenRepository;
import com.matt.repository.ClassesRepository;
import com.matt.repository.base.BaseNativeSqlRepository;
import com.matt.repository.base.ChildChildrenCourseItemRepository;
import com.matt.repository.base.ChildrenMonthRecordV2Repository;
import com.matt.repository.courseV2.ChildrenMonthRecordRepository;
import com.matt.repository.rel.RelClassesChildrenRepository;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageImpl;
import org.springframework.data.domain.PageRequest;
import org.springframework.stereotype.Component;

import java.util.*;
import java.util.stream.Collectors;

/**
 * *******************************
 * Created by Ron.
 * form com.matt
 * on 2017/9/11
 * *******************************
 */
@Component
public class ChildrenMonthRecordV2RepositoryImpl extends BaseNativeSqlRepository implements ChildrenMonthRecordV2Repository {


    @Autowired
    private BaseNativeSqlRepository baseNativeSqlRepository;

    @Autowired
    private ChildrenRepository childrenRepository;

    @Autowired
    protected MemVerificationCache cache;

    @Autowired
    protected ChildrenMonthRecordRepository childrenMonthRecordRepository;

    @Autowired
    private RelClassesChildrenRepository relClassesChildrenRepository;

    @Override
    public List<Object[]> findChildIdAvg(String childrenId, String date) {
        String sql = "SELECT IFNULL(AVG(sleepTime),0),IFNULL(SUM(sleepTime),0),COUNT(childId)  FROM  " +
                "(SELECT childId ,TIMESTAMPDIFF(MINUTE,(DATE_FORMAT(sleepTime,'%Y-%m-%d %H:%i')),(DATE_FORMAT(wakeupTime, '%Y-%m-%d %H:%i'))) as sleepTime FROM hl_sleeping_record  " +
                " WHERE childId = '" + childrenId + "' AND DATE_FORMAT(sleepTime,'%Y-%m') = DATE_FORMAT('"+ date+"','%Y-%m')) a ";
        List<Object[]> objects = sqlArrayList(sql);
        return objects;
    }

    @Override
    public List<Object[]> findChildIdByFoodCount(String childrenId, String date) {
        String sql = "SELECT  *  FROM ( SELECT food,COUNT(*) foodCount FROM `hl_feeding_record` WHERE food is not null " +
                " and  childId = '" + childrenId + "' AND DATE_FORMAT(created,'%Y-%m') = DATE_FORMAT('"+ date+"','%Y-%m') GROUP BY food ) a  ORDER BY foodCount DESC LIMIT 1";
        List<Object[]> objects = sqlArrayList(sql);
        return objects;
    }

    @Override
    public List<Object[]> findChildIdByDietAmountAndDietCount(String childrenId, String date,String food) {
        String sql = "SELECT IFNULL(AVG(liquidQuantity),0),IFNULL(AVG(foodCount),0) FROM " +
                "(SELECT DATE_FORMAT( created, '%Y-%m-%d') AS m,liquidQuantity ,COUNT(id) foodCount FROM `hl_feeding_record`  WHERE food is not null " +
                " and  childId = '" + childrenId + "' AND DATE_FORMAT(created,'%Y-%m') = DATE_FORMAT('"+ date+"','%Y-%m' )  and food = '"+food+"' GROUP BY m ) a ";
        List<Object[]> objects = sqlArrayList(sql);
        return objects;
    }

    @Override
    public List<Object[]> findChildrenMonthBody(String childrenId) {

        String sql1 = "SELECT DATE_FORMAT(er.created, '%Y-%m') AS m, er.childrenId ,IFNULL(MAX(er.weight),0) weight, IFNULL(MAX(er.height),0) height, IFNULL(MAX(er.header),0) header,er.created  " +
                "  FROM tb_evaluate_record er WHERE er.childrenId = '"   + childrenId +  "' " +
                "    GROUP BY m  ";
        List<Object[]> objects2  =sqlArrayList(sql1);

        String sql2 = "SELECT DATE_FORMAT(cr.created, '%Y-%m') AS m, cr.childrenId,IFNULL(MAX(cr.weight),0) weight, IFNULL(MAX(cr.height),0) height, IFNULL(MAX(cr.header),0) header,cr.created " +
                "FROM check_record  cr  WHERE cr.childrenId = '" + childrenId + "' " +
                "GROUP BY m ";
        List<Object[]> objects  =sqlArrayList(sql2);

        List<Object[]> objects1  = new ArrayList<>();
        List<Object[]> objects3  = new ArrayList<>();
        if (objects2.size()>0){
            for (int i = 0; i <objects2.size() ; i++) {
                Object[] v = objects2.get(i);
                for (int j = 0; j <objects.size() ; j++) {
                    if (StringUtils.equals(objects.get(j)[0].toString(),objects2.get(i)[0].toString())){
                        if (((Date)objects.get(j)[5]).getTime() > ((Date)objects.get(i)[5]).getTime() ){
                            v =  objects.get(j);
                        }
                    }
                }
                objects1.add(v);
            }
            objects3.addAll(objects1);
            for (int i = 0; i <objects.size() ; i++) {
                boolean  v = true;
                for (int j = 0; j <objects1.size() ; j++) {
                    if (StringUtils.equals(objects.get(i)[0].toString(),objects1.get(j)[0].toString())){
                        v = false;
                        continue;
                    }
                }
                if (v){
                    objects3.add(objects.get(i));
                }
            }
        }

        return objects3;
    }

    @Override
    public Page<ChildrenMonthRecordVO> getList(StaffDTO staffDTO, String childrenId, Integer month, String classId, String name, PagerQuery pagerQuery) {

        String sql = "SELECT cmr.id, cmr.created,  cmr.childrenId, ifnull(cmr.childrenMonth,'0') childrenMonth, cmr.content ,cmr.createdBy ,cmr.createdName,cmr.areaId ,c.name,ifnull(c.height,'0') height,ifnull(c.weight,'0') weight  FROM tc_children_month_record cmr inner join children c    on  cmr.childrenId = c.id where cmr.deleted = 0 ";

        sql += String.format("  and cmr.areaId = '%s' ", staffDTO.getAreaId());

        if (AssertUtil.isNotEmpty(childrenId)) {
            sql += String.format("  and cmr.childrenId = '%s' ", childrenId);
        }
        if (AssertUtil.isNotEmpty(month)) {
            sql += String.format("  and cmr.month = %s ", month);
        }

        if (AssertUtil.isNotEmpty(classId)) {
            List<RelClassesChildren>  classesChildren =  relClassesChildrenRepository.findAllByClassesId(classId);
            List<String> childrenIds = classesChildren.stream().map(k->k.getChildrenId()).collect(Collectors.toList());
            String ids = UuidUtil.getString(childrenIds);
            if (classesChildren.size()>0){
                sql += "  and cmr.childrenId in ("+ids+") ";
            }else {
                return  new PageImpl<>(new ArrayList<>(), PageRequest.of(pagerQuery.getPageNum(), pagerQuery.getPageSize()), 0);
            }

        }

        if (Objects.nonNull(name)) {
            List<Children>  childrenList = childrenRepository.findByAreaIdAndNameLikeAndDeleted( staffDTO.getAreaId(), name, false);
            List<String> childrenIds = childrenList.stream().map(k->k.getId()).collect(Collectors.toList());
            String ids = UuidUtil.getString(childrenIds);
            if (childrenList.size()>0){
                sql += "  and cmr.childrenId in ("+ids+") ";
            }else {
                return  new PageImpl<>(new ArrayList<>(), PageRequest.of(pagerQuery.getPageNum(), pagerQuery.getPageSize()), 0);
            }
        }

        sql += "  ORDER BY cmr.childrenMonth  desc  ";
        List<Object[]> objects1 = baseNativeSqlRepository.sqlArrayList(sql);

        sql += String.format("  limit %s, %s ", pagerQuery.getPageNum() * pagerQuery.getPageSize() ,  pagerQuery.getPageSize());

        List<Object[]> objects = baseNativeSqlRepository.sqlArrayList(sql);

        List<ChildrenMonthRecordVO> list  = new ArrayList<>();
        for (int j = 0; j < objects.size(); j++) {
            ChildrenMonthRecordVO g = new ChildrenMonthRecordVO();
            g.setId(objects.get(j)[0].toString());
            g.setCreated((Date) objects.get(j)[1]);
            g.setChildrenId(objects.get(j)[2].toString());
            g.setChildrenMonth(Integer.valueOf(objects.get(j)[3].toString()));
//            g.setContent();
            String originalContent = objects.get(j)[4].toString();
            g.setCreatedBy(objects.get(j)[5].toString());
            g.setCreatedName(objects.get(j)[6].toString());
            g.setAreaId(objects.get(j)[7].toString());
            g.setChildrenName(objects.get(j)[8].toString());
            String height = objects.get(j)[9].toString();
            String weight = objects.get(j)[10].toString();

            // 使用Fastjson替换content中的值
            try {
                JSONObject jsonContent = JSON.parseObject(originalContent);  // 解析为JSON对象
                jsonContent.put("height", height);  // 替换height值
                jsonContent.put("weight", weight);  // 替换weight值
                g.setContent(jsonContent.toJSONString());  // 更新为修改后的JSON字符串
            } catch (Exception e) {
                // 解析失败时保留原始content
                g.setContent(originalContent);
                e.printStackTrace();  // 可选：记录错误日志
            }

            list.add(g);
        }

        return  new PageImpl<>(list, PageRequest.of(pagerQuery.getPageNum(), pagerQuery.getPageSize()), objects1.size());
    }



    @Override
    public Page<ChildrenMonthRecordVO> getList1(String areaId, String childrenId, Integer month, String classId, String name, PagerQuery pagerQuery) {

        String sql = "SELECT cmr.id, cmr.created,  cmr.childrenId, ifnull(cmr.childrenMonth,'0') childrenMonth, cmr.content ,cmr.createdBy ,cmr.createdName,cmr.areaId ,c.name,ifnull(c.height,'0') height,ifnull(c.weight,'0') weight  FROM tc_children_month_record cmr inner join children c    on  cmr.childrenId = c.id where cmr.deleted = 0 ";

        if (AssertUtil.isNotEmpty(areaId)) {
            sql += String.format("  and cmr.areaId = '%s' ", areaId);
        }

        if (AssertUtil.isNotEmpty(childrenId)) {
            sql += String.format("  and cmr.childrenId = '%s' ", childrenId);
        }
        if (AssertUtil.isNotEmpty(month)) {
            sql += String.format("  and cmr.month = %s ", month);
        }

        if (AssertUtil.isNotEmpty(classId)) {
            List<RelClassesChildren>  classesChildren =  relClassesChildrenRepository.findAllByClassesId(classId);
            List<String> childrenIds = classesChildren.stream().map(k->k.getChildrenId()).collect(Collectors.toList());
            String ids = UuidUtil.getString(childrenIds);
            if (classesChildren.size()>0){
                sql += "  and cmr.childrenId in ("+ids+") ";
            }else {
                return  new PageImpl<>(new ArrayList<>(), PageRequest.of(pagerQuery.getPageNum(), pagerQuery.getPageSize()), 0);
            }

        }

        if (Objects.nonNull(name)) {
            List<Children>  childrenList = childrenRepository.findByAreaIdAndNameLikeAndDeleted( areaId, name, false);
            List<String> childrenIds = childrenList.stream().map(k->k.getId()).collect(Collectors.toList());
            String ids = UuidUtil.getString(childrenIds);
            if (childrenList.size()>0){
                sql += "  and cmr.childrenId in ("+ids+") ";
            }else {
                return  new PageImpl<>(new ArrayList<>(), PageRequest.of(pagerQuery.getPageNum(), pagerQuery.getPageSize()), 0);
            }
        }

        sql += "  ORDER BY cmr.childrenMonth  desc  ";
        List<Object[]> objects1 = baseNativeSqlRepository.sqlArrayList(sql);

        sql += String.format("  limit %s, %s ", pagerQuery.getPageNum() * pagerQuery.getPageSize() ,  pagerQuery.getPageSize());

        List<Object[]> objects = baseNativeSqlRepository.sqlArrayList(sql);

        List<ChildrenMonthRecordVO> list  = new ArrayList<>();
        for (int j = 0; j < objects.size(); j++) {
            ChildrenMonthRecordVO g = new ChildrenMonthRecordVO();
            g.setId(objects.get(j)[0].toString());
            g.setCreated((Date) objects.get(j)[1]);
            g.setChildrenId(objects.get(j)[2].toString());
            g.setChildrenMonth(Integer.valueOf(objects.get(j)[3].toString()));
//            g.setContent();
            String originalContent = objects.get(j)[4].toString();
            g.setCreatedBy(objects.get(j)[5].toString());
            g.setCreatedName(objects.get(j)[6].toString());
            g.setAreaId(objects.get(j)[7].toString());
            g.setChildrenName(objects.get(j)[8].toString());
            String height = objects.get(j)[9].toString();
            String weight = objects.get(j)[10].toString();

            // 使用Fastjson替换content中的值
            try {
                JSONObject jsonContent = JSON.parseObject(originalContent);  // 解析为JSON对象
                jsonContent.put("height", height);  // 替换height值
                jsonContent.put("weight", weight);  // 替换weight值
                g.setContent(jsonContent.toJSONString());  // 更新为修改后的JSON字符串
            } catch (Exception e) {
                // 解析失败时保留原始content
                g.setContent(originalContent);
                e.printStackTrace();  // 可选：记录错误日志
            }

            list.add(g);
        }

        return  new PageImpl<>(list, PageRequest.of(pagerQuery.getPageNum(), pagerQuery.getPageSize()), objects1.size());
    }


}
